package com.huabo.contract.oracle.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.oracle.entity.TblLegalArbitratsettlement;

public class TblLegalArbitratsettlementMapperSqlConfig {


    public String findListByPageInfo(PageInfo<TblLegalArbitratsettlement> pageInfo, TblLegalArbitratsettlement negotia,Integer disputeid) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT * FROM TBL_LEGAL_ARBITRATSETTLEMENT TLA\n" +
                "LEFT JOIN TBL_LEGAL_NEGOTIATEDSETTLEMEN TLN ON TLA.NEGOTIATEINFO = TLN.NEGOTIAID\n" +
                "LEFT JOIN TBL_LEGAL_DISPUTREGISTRATION TLD ON TLN.DISPUINFO = TLD.DISPUTEID\n" +
                "WHERE TLA.LINKORG = " + negotia.getLinkorg());
        if ( disputeid != null){
            sqlSb.append(" AND TLD.DISPUTEID = " + disputeid );
        }
        if (negotia.getCourtfirst() != null) {
            sqlSb.append(" AND TLA.COURTFIRST like '%" + negotia.getCourtfirst() + "%' ");
        }
        if (negotia.getStartdate() != null && !"".equals(negotia.getStartdate())) {
            sqlSb.append(" AND TLA.ASDEALDATE >= to_date('" + negotia.getStartdate() + "','yyyy-mm-dd') ");
        }

        if (negotia.getEnddate() != null  && !"".equals(negotia.getEnddate())) {
            sqlSb.append(" AND TLA.ASDEALDATE <= to_date('" + negotia.getEnddate() + "','yyyy-mm-dd') ");
        }

        sqlSb.append(" ORDER BY TLA.ARBITRAID DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }

    public String findListByPageInfoCount(TblLegalArbitratsettlement negotia,Integer disputeid) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*) FROM TBL_LEGAL_ARBITRATSETTLEMENT TLA\n" +
                "LEFT JOIN TBL_LEGAL_NEGOTIATEDSETTLEMEN TLN ON TLA.NEGOTIATEINFO = TLN.NEGOTIAID\n" +
                "LEFT JOIN TBL_LEGAL_DISPUTREGISTRATION TLD ON TLN.DISPUINFO = TLD.DISPUTEID\n" +
                "WHERE TLA.LINKORG = " + negotia.getLinkorg() );
        if ( disputeid != null){
            sqlSb.append(" AND TLD.DISPUTEID = " + disputeid );
        }
        if (negotia.getCourtfirst() != null) {
            sqlSb.append(" AND TLA.COURTFIRST like '%" + negotia.getCourtfirst() + "%' ");
        }
        if (negotia.getStartdate() != null && !"".equals(negotia.getStartdate())) {
            sqlSb.append(" AND TLA.ASDEALDATE >= to_date('" + negotia.getStartdate() + "','yyyy-mm-dd') ");
        }

        if (negotia.getEnddate() != null  && !"".equals(negotia.getEnddate())) {
            sqlSb.append(" AND TLA.ASDEALDATE <= to_date('" + negotia.getEnddate() + "','yyyy-mm-dd') ");
        }

        sqlSb.append(" ORDER BY TLA.ARBITRAID DESC");
        String sql = sqlSb.toString();
        return sql;
    }


    public String saveDiputergistration(TblLegalArbitratsettlement arbitrat) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_LEGAL_ARBITRATSETTLEMENT (ARBITRAID");
        StringBuffer value = new StringBuffer(" VALUES (MYTASK_SEQUENCE.nextval");

        if(arbitrat.getCourtfirst() != null && !"".equals(arbitrat.getCourtfirst())){
            column.append(",COURTFIRST");
            value.append(",'"+arbitrat.getCourtfirst()+"'");
        }
        if(arbitrat.getAsdealdate() != null && !"".equals(arbitrat.getAsdealdate())){
            column.append(",ASDEALDATE");
            //value.append(",'"+arbitrat.getAsdealdate()+"'");
            value.append(",TO_DATE('"+ DateUtil.parseDate(arbitrat.getAsdealdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
            //value.append(",TO_DATE('"+ DateUtil.parseDate(arbitrat.getAsdealdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(arbitrat.getAsfirsthearingdate() != null && !"".equals(arbitrat.getAsfirsthearingdate())){
            column.append(",ASFIRSTHEARINGDATE");
           // value.append(",'"+arbitrat.getAsfirsthearingdate()+"'");
            value.append(",TO_DATE('"+ DateUtil.parseDate(arbitrat.getAsfirsthearingdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(arbitrat.getArbitrationamount() != null && !"".equals(arbitrat.getArbitrationamount())){
            column.append(",ARBITRATIONAMOUNT");
            value.append(",'"+arbitrat.getArbitrationamount()+"'");
        }
        if(arbitrat.getArbitrationenddate() != null && !"".equals(arbitrat.getArbitrationenddate())){
            column.append(",ARBITRATIONENDDATE");
            //value.append(",'"+arbitrat.getArbitrationenddate()+"'");
            value.append(",TO_DATE('"+ DateUtil.parseDate(arbitrat.getArbitrationenddate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(arbitrat.getArbitrationresult() != null && !"".equals(arbitrat.getArbitrationresult())){
            column.append(",ARBITRATIONRESULT");
            value.append(",'"+arbitrat.getArbitrationresult()+"'");
        }
        if(arbitrat.getNegotiateinfo() != null && !"".equals(arbitrat.getNegotiateinfo())){
            column.append(",NEGOTIATEINFO");
            value.append(",'"+arbitrat.getNegotiateinfo()+"'");
        }
        if(arbitrat.getCreatestaff() != null && !"".equals(arbitrat.getCreatestaff())){
            column.append(",CREATESTAFF");
            value.append(",'"+arbitrat.getCreatestaff()+"'");
        }
//        if(arbitrat.getCreatetime() != null && !"".equals(arbitrat.getCreatetime())){
//            column.append(",CREATETIME");
//          //  value.append(",'"+arbitrat.getCreatetime()+"'");
//            value.append(",CREATETIME('"+ DateUtil.parseDate(arbitrat.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
//        }
        if(arbitrat.getLinkorg() != null && !"".equals(arbitrat.getLinkorg())){
            column.append(",LINKORG");
            value.append(",'"+arbitrat.getLinkorg()+"'");
        }
        if(arbitrat.getArbitrastatus() != null && !"".equals(arbitrat.getArbitrastatus())){
            column.append(",ARBITRASTATUS");
            value.append(",'"+arbitrat.getArbitrastatus()+"'");
        }
        if(arbitrat.getCurrency() != null && !"".equals(arbitrat.getCurrency())){
            column.append(",CURRENCY");
            value.append(",'"+arbitrat.getCurrency()+"'");
        }
        if(arbitrat.getDisputeid() != null && !"".equals(arbitrat.getDisputeid())){
            column.append(",DISPUTEID");
            value.append(",'"+arbitrat.getDisputeid()+"'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }


    public String modifyNegotiatedSettlement(TblLegalArbitratsettlement oldArbitrat) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_LEGAL_ARBITRATSETTLEMENT SET COURTFIRST = '"+oldArbitrat.getCourtfirst()+"'");
        if(oldArbitrat.getAsdealdate() != null && !"".equals(oldArbitrat.getAsdealdate())) {
            sql.append(" ,ASDEALDATE = TO_DATE('"+DateUtil.parseDate(oldArbitrat.getAsdealdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(oldArbitrat.getAsfirsthearingdate() != null && !"".equals(oldArbitrat.getAsfirsthearingdate())) {
            sql.append(" ,ASFIRSTHEARINGDATE = TO_DATE('"+DateUtil.parseDate(oldArbitrat.getAsfirsthearingdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(oldArbitrat.getArbitrationamount() != null && !"".equals(oldArbitrat.getArbitrationamount())) {
            sql.append(" , ARBITRATIONAMOUNT = '"+oldArbitrat.getArbitrationamount()+"'");
        }
        if(oldArbitrat.getArbitrationenddate() != null && !"".equals(oldArbitrat.getArbitrationenddate())) {
            sql.append(" ,ARBITRATIONENDDATE = TO_DATE('"+DateUtil.parseDate(oldArbitrat.getArbitrationenddate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(oldArbitrat.getArbitrationresult() != null && !"".equals(oldArbitrat.getArbitrationresult())) {
            sql.append(" , ARBITRATIONRESULT = '"+oldArbitrat.getArbitrationresult()+"'");
        }
        if(oldArbitrat.getNegotiate() != null && !"".equals(oldArbitrat.getNegotiate())) {
            sql.append(" , NEGOTIATEINFO = '"+oldArbitrat.getNegotiate()+"'");
        }
        if(oldArbitrat.getCreatestaff() != null && !"".equals(oldArbitrat.getCreatestaff())) {
            sql.append(" , CREATESTAFF = '"+oldArbitrat.getCreatestaff()+"'");
        }
        if(oldArbitrat.getCreatetime() != null && !"".equals(oldArbitrat.getCreatetime())) {
            sql.append(" ,CREATETIME = TO_DATE('"+DateUtil.parseDate(oldArbitrat.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(oldArbitrat.getLinkorg() != null && !"".equals(oldArbitrat.getLinkorg())) {
            sql.append(" , LINKORG = '"+oldArbitrat.getLinkorg()+"'");
        }
        if(oldArbitrat.getArbitrastatus() != null && !"".equals(oldArbitrat.getArbitrastatus())) {
            sql.append(" , ARBITRASTATUS = '"+oldArbitrat.getArbitrastatus()+"'");
        }
        if(oldArbitrat.getCurrency() != null && !"".equals(oldArbitrat.getCurrency())) {
            sql.append(" , CURRENCY = '"+oldArbitrat.getCurrency()+"'");
        }
//        if(oldArbitrat.getDisputeid() != null && !"".equals(oldArbitrat.getDisputeid())) {
//            sql.append(" , DISPUTEID = '"+oldArbitrat.getDisputeid()+"'");
//        }

        sql.append(" WHERE ARBITRAID = '"+oldArbitrat.getArbitraid()+"'");
        return sql.toString();
    }
}
